ALTER PROCEDURE sp_save_cost_code_details (
@costCode VARCHAR(50),
@assy     VARCHAR(50),
@base     VARCHAR(50),
@pkg      VARCHAR(50),
@pin      VARCHAR(50),
@pkgDesc  VARCHAR(50),
@waferCost NUMERIC(18,3),
@dieCost   NUMERIC(18,3),
@assmCost  NUMERIC(18,3),
@testCost  NUMERIC(18,3),
@trCost    NUMERIC(18,3),
@fgCost    NUMERIC(18,3),
@year		INT,
@quater		INT,
@CCID    INT
)

AS
BEGIN
		DECLARE @START_DATE 	DATETIME
    	DECLARE @END_DATE 	DATETIME
    	DECLARE @TEMP_VARCHAR 	VARCHAR(20)
    	    
   		SET @TEMP_VARCHAR = CONVERT(VARCHAR, CURRENT_TIMESTAMP, 101)+' 12:00AM'
    	SET @START_DATE = CONVERT(DATETIME, @TEMP_VARCHAR)
    	SET @TEMP_VARCHAR = NULL
    	SET @TEMP_VARCHAR = CONVERT(VARCHAR, CURRENT_TIMESTAMP, 101)+' 11:59PM' 
    	SET @END_DATE = CONVERT(DATETIME, @TEMP_VARCHAR)

					
		--select * from Cost_Code_table
		IF ( ( SELECT COUNT(*) FROM COST_CODE_TABLE WHERE CC_ID = @CCID AND YEAR = @year  AND QTR = @quater ) = 0 )
		BEGIN
			INSERT INTO COST_CODE_TABLE ( Cost_Code, Qtr, Year, Die_Cost, Assm_Cost,Test_Cost,Fg_Cost, TR_Cost,
											Wafer_Cost, Assy, Base, Pkg, Pin, pkg_Desc) 
				VALUES ( @costCode, @quater, @year, @dieCost, @assmCost, @testCost, @fgCost, @trCost, @waferCost,@assy,
						  @base, @pkg, @pin, @pkgDesc )

			INSERT INTO Cost_Code_History_Table SELECT CC_ID, Cost_Code, Qtr, Year, Die_Cost, Assm_Cost,Test_Cost,Fg_Cost, TR_Cost,
											Wafer_Cost, Assy, Base, Pkg, Pin, pkg_Desc,CURRENT_TIMESTAMP from COST_CODE_TABLE WHERE CC_ID = @CCID AND YEAR = @year  AND QTR = @quater  
		END
		ELSE
		BEGIN
			IF ( ( SELECT COUNT(*) FROM Cost_Code_History_Table WHERE CC_ID = @CCID AND YEAR = @year  AND QTR = @quater AND Cost_Code_History_Table.Timestamp BETWEEN @START_DATE AND @END_DATE ) > 0 )
			BEGIN
					UPDATE Cost_Code_History_Table SET COST_CODE = @costCode,Die_Cost = @dieCost, Assm_Cost = @assmCost, Test_Cost = @testCost, Fg_Cost = @fgCost, TR_Cost = @trCost ,
						Wafer_Cost = @waferCost, Assy = @assy, base = @base, pkg =@pkg, pin = @pin, pkg_Desc = @pkgDesc ,TimeStamp = CURRENT_TIMESTAMP
						WHERE CC_ID = @CCID AND YEAR = @year  AND QTR = @quater AND Cost_Code_History_Table.Timestamp BETWEEN @START_DATE AND @END_DATE 		
			END
			ELSE
			BEGIN
				INSERT INTO Cost_Code_History_Table SELECT CC_ID, Cost_Code, Qtr, Year, Die_Cost, Assm_Cost,Test_Cost,Fg_Cost, TR_Cost,
											Wafer_Cost, Assy, Base, Pkg, Pin, pkg_Desc,CURRENT_TIMESTAMP from COST_CODE_TABLE WHERE CC_ID = @CCID AND YEAR = @year  AND QTR = @quater
			END
	
			UPDATE COST_CODE_TABLE SET COST_CODE = @costCode, Die_Cost = @dieCost, Assm_Cost = @assmCost, Test_Cost = @testCost, Fg_Cost = @fgCost, TR_Cost = @trCost ,
				Wafer_Cost = @waferCost, Assy = @assy, base = @base, pkg =@pkg, pin = @pin, pkg_Desc = @pkgDesc 
				WHERE CC_ID=@CCID AND YEAR = @year  AND QTR = @quater
			
		END

END


